﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using MySql.Data.MySqlClient;
using YmtAuth.Domain.Model.Safe;
using YmtAuth.Domain.Repository;
using YmtAuth.Common;

namespace YmtAuth.Repository.MySQL
{
    public class IpAreaInfoRepository : MySQLRepository<IpAreaInfoAuthEntity>, IIpAreaInfoRepository
    {
        public IpAreaInfoRepository()
            : base(ConfigurationManager.AppSettings["UserIPAuth_MySQLConnString"], "UserIPAuth")
        {
        }

        public IpAreaInfoAuthEntity.IpAreaInfo FindIpAreaInfo(long ipStart)
        {
            using (var conn = new MySqlConnection(ConfigurationManager.AppSettings["IpArea_MySQLConnString"]))
            {
                return
                    conn.Query<IpAreaInfoAuthEntity.IpAreaInfo>(
                        "select ipbeginNum as Start,country as Country ,province as Province ,city as City from ip_chunhui where ipbeginNum<=@ip_start AND ipendNum>=@ip_start order by ipendNum  limit 1"
                        , Parameters.AddLong("@ip_start", ipStart).Build(), commandTimeout: ConfigUtility.LoginSafeSQLCommandTimeOut)
                        .SingleOrDefault();              
            }
        }

        public bool IpAreaInfoExists(long ipStart)
        {
            using (var conn = new MySqlConnection(ConfigurationManager.AppSettings["IpArea_MySQLConnString"]))
            {
                 return
                    conn.Query<int>(
                        "select 1 from ip_chunhui where ipbeginNum<=@ip_start order by ipbeginNum desc limit 1"
                        , Parameters.AddLong("@ip_start", ipStart).Build(), commandTimeout: ConfigUtility.LoginSafeSQLCommandTimeOut)
                        .SingleOrDefault()==1;   
            }
        }
        public bool AreaInfoAuthEntityExists(int userid,string country, string province, string city)
        {
            var par = this.Parameters.AddInt("@UserId", userid)
                        .AddString("@Country", country)
                        .AddString("@Province", province)
                        .AddString("@City", city)
                        .Build();
            using (var conn = new MySqlConnection(this.connString))
            {
                return
                   conn.Query<int>(
                       "select 1 from UserIPAuth where `UserId`=@UserId and `Country`=@Country and `Province`=@Province and `City`=@City limit 1"
                       , par)
                       .SingleOrDefault() == 1;
            }
        }

        public IpAreaInfoAuthEntity FindAreaInfoAuthEntity(int userid, string country, string province, string city)
        {
            var par = this.Parameters.AddInt("@UserId", userid)
                .AddString("@Country", country)
                .AddString("@Province", province)
                .AddString("@City", city)
                .Build();

            return
                Query(
                    "select `UserId`,`Province`,`City`,`AuthStatus`,`AuthTime`,`Country`,`CreateTime`,`AppSource`,`LastLoginTime`,`LastUpdateTime`, `IPAreaSource`, `Ip` from UserIPAuth where `UserId`=@UserId and `Country`=@Country and `Province`=@Province and `City`=@City",
                    par, commandType: CommandType.Text).FirstOrDefault();
        }
        public IpAreaInfoAuthEntity FindAreaInfoAuthEntity(int userid, string country, string province,int status)
        {
            var par = this.Parameters.AddInt("@UserId", userid)
                .AddString("@Country", country)
                .AddString("@Province", province)
                .AddInt("@AuthStatus", status)
                .Build();

            return
                Query(
                    "select `UserId`,`Province`,`City`,`AuthStatus`,`AuthTime`,`Country`,`CreateTime`,`AppSource`,`LastLoginTime`,`LastUpdateTime`, `IPAreaSource`, `Ip` from UserIPAuth where `UserId`=@UserId and `Country`=@Country and `Province`=@Province AND `AuthStatus`=@AuthStatus limit 1",
                    par, commandType: CommandType.Text).FirstOrDefault();
        }

        public IpAreaInfoAuthEntity FindAreaInfoAuthEntity(int userid, string country, string province, string city, int status)
        {
            var par = this.Parameters.AddInt("@UserId", userid)
                .AddString("@Country", country)
                .AddString("@Province", province)
               .AddInt("@AuthStatus", status)
               .AddString("@City", city)
                .Build();

            return
                Query(
                    "select `UserId`,`Province`,`City`,`AuthStatus`,`AuthTime`,`Country`,`CreateTime`,`AppSource`,`LastLoginTime`,`LastUpdateTime`, `IPAreaSource`, `Ip` from UserIPAuth where `UserId`=@UserId and `Country`=@Country and `Province`=@Province AND `City`=@City AND `AuthStatus`=@AuthStatus limit 1",
                    par, commandType: CommandType.Text).FirstOrDefault();
        }

        public IpAreaInfoAuthEntity FindAreaInfoAuthEntity(int userid, string country, int status)
        {
            var par = this.Parameters.AddInt("@UserId", userid)
                .AddString("@Country", country)               
                .AddInt("@AuthStatus", status)
                .Build();

            return
                Query(
                    "select `UserId`,`Province`,`City`,`AuthStatus`,`AuthTime`,`Country`,`CreateTime`,`AppSource`,`LastLoginTime`,`LastUpdateTime`, `IPAreaSource`, `Ip` from UserIPAuth where `UserId`=@UserId and `Country`=@Country AND `AuthStatus`=@AuthStatus limit 1",
                    par, commandType: CommandType.Text).FirstOrDefault();
        }
        public IEnumerable<IpAreaInfoAuthEntity> FindUserFirstAuthAreaInfo(int userid,int authStatus)
        {
            var par = this.Parameters.AddInt("@UserId", userid).Build();
           
                return
                    Query(
                        "select `UserId`,`Province`,`City`,`AuthStatus`,`AuthTime`,`Country`,`CreateTime`,`AppSource`,`LastLoginTime`,`LastUpdateTime`, `IPAreaSource`, `Ip` from UserIPAuth where `UserId`=@UserId AND `AuthStatus` in(200,-200) limit 1",
                        par, commandType: CommandType.Text);
            
        }

        public DateTime? FindUserTokenLastAuthDateTime(int userid)
        {
            var par = this.Parameters.AddInt("@UserId", userid).Build();
            using (
                var conn = new MySqlConnection(ConfigurationManager.AppSettings["UserLoginIdentifyAuth_MySQLConnString"])
                )
            {
                return
                    conn.Query<DateTime?>(
                        "select `LastAuthTime` from UserTokenAuthHistory  where `UserId`=@UserId  order by LastAuthTime desc LIMIT 1",
                        par, commandType: CommandType.Text, commandTimeout: ConfigUtility.LoginSafeSQLCommandTimeOut).FirstOrDefault();
            }
        }

        public IpAreaInfoAuthEntity FindUserLastTokenAuthIpAreaInfo(int userid, string country, string province, string city)
        {
            var par =
               this.Parameters.AddInt("@UserId", userid)
                   .AddString("@Country", country)
                   .AddString("@Province", province)
                   .AddString("@City", city).Build();
            return
               Query(
                   "select `UserId`,`Province`,`City`,`AuthStatus`,`AuthTime`,`Country`,`CreateTime`,`AppSource`,`LastLoginTime`,`LastUpdateTime`, `IPAreaSource`, `Ip` from UserIPAuth where `UserId`=@UserId and `Country`=@Country and `Province`=@Province and `City`=@City order by AuthTime desc limit 1",
                   par, commandType: CommandType.Text).FirstOrDefault();
        }

        /// <summary>
        /// 更新用户IP区域认证状态为“已认证”
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="country"></param>
        /// <param name="province"></param>
        /// <param name="city"></param>
        /// <returns></returns>
        public int AuthIpAreaInfoSuccess(int userid, string country, string province, string city)
        {
            var par =
               this.Parameters.AddInt("@UserId", userid)
                   .AddString("@Country", country)
                   .AddString("@Province", province)
                   .AddString("@City", city).Build();
            string updateSql = "UPDATE useripauth SET AuthStatus = 200, `AuthTime` = now(), `LastUpdateTime` = now() where `UserId`=@UserId and `Country`=@Country and `Province`=@Province and `City`=@City";

            using (var conn = new MySqlConnection(this.connString))
            {
                return conn.Execute(updateSql, par, commandTimeout: ConfigUtility.LoginSafeSQLCommandTimeOut);
            }
        }
    }
}
